{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Aggregations with pandas and numpy\n",
    "\n",
    "## About the Data\n",
    "In this notebook, we will be working with 2 data sets:\n",
    "- Facebook's stock price throughout 2018 (obtained using the [`stock_analysis` package](https://github.com/stefmolin/stock-analysis)).\n",
    "- daily weather data for NYC from the [National Centers for Environmental Information (NCEI) API](https://www.ncdc.noaa.gov/cdo-web/webservices/v2).\n",
    "\n",
    "*Note: The NCEI is part of the National Oceanic and Atmospheric Administration (NOAA) and, as you can see from the URL for the API, this resource was created when the NCEI was called the NCDC. Should the URL for this resource change in the future, you can search for the NCEI weather API to find the updated one.*\n",
    "\n",
    "## Background on the weather data\n",
    "\n",
    "Data meanings:\n",
    "- `AWND`: average wind speed\n",
    "- `PRCP`: precipitation in millimeters\n",
    "- `SNOW`: snowfall in millimeters\n",
    "- `SNWD`: snow depth in millimeters\n",
    "- `TMAX`: maximum daily temperature in Celsius\n",
    "- `TMIN`: minimum daily temperature in Celsius\n",
    "\n",
    "## Setup"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>datatype</th>\n",
       "      <th>station</th>\n",
       "      <th>value</th>\n",
       "      <th>station_name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2018-01-01</th>\n",
       "      <td>PRCP</td>\n",
       "      <td>GHCND:US1CTFR0039</td>\n",
       "      <td>0.0</td>\n",
       "      <td>STAMFORD 4.2 S, CT US</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-01</th>\n",
       "      <td>PRCP</td>\n",
       "      <td>GHCND:US1NJBG0015</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NORTH ARLINGTON 0.7 WNW, NJ US</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-01</th>\n",
       "      <td>SNOW</td>\n",
       "      <td>GHCND:US1NJBG0015</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NORTH ARLINGTON 0.7 WNW, NJ US</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-01</th>\n",
       "      <td>PRCP</td>\n",
       "      <td>GHCND:US1NJBG0017</td>\n",
       "      <td>0.0</td>\n",
       "      <td>GLEN ROCK 0.7 SSE, NJ US</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-01</th>\n",
       "      <td>SNOW</td>\n",
       "      <td>GHCND:US1NJBG0017</td>\n",
       "      <td>0.0</td>\n",
       "      <td>GLEN ROCK 0.7 SSE, NJ US</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           datatype            station  value                    station_name\n",
       "date                                                                         \n",
       "2018-01-01     PRCP  GHCND:US1CTFR0039    0.0           STAMFORD 4.2 S, CT US\n",
       "2018-01-01     PRCP  GHCND:US1NJBG0015    0.0  NORTH ARLINGTON 0.7 WNW, NJ US\n",
       "2018-01-01     SNOW  GHCND:US1NJBG0015    0.0  NORTH ARLINGTON 0.7 WNW, NJ US\n",
       "2018-01-01     PRCP  GHCND:US1NJBG0017    0.0        GLEN ROCK 0.7 SSE, NJ US\n",
       "2018-01-01     SNOW  GHCND:US1NJBG0017    0.0        GLEN ROCK 0.7 SSE, NJ US"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "\n",
    "weather = pd.read_csv('data/weather_by_station.csv', index_col='date', parse_dates=True)\n",
    "weather.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>open</th>\n",
       "      <th>high</th>\n",
       "      <th>low</th>\n",
       "      <th>close</th>\n",
       "      <th>volume</th>\n",
       "      <th>trading_volume</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2018-01-02</th>\n",
       "      <td>177.68</td>\n",
       "      <td>181.58</td>\n",
       "      <td>177.5500</td>\n",
       "      <td>181.42</td>\n",
       "      <td>18151903</td>\n",
       "      <td>low</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-03</th>\n",
       "      <td>181.88</td>\n",
       "      <td>184.78</td>\n",
       "      <td>181.3300</td>\n",
       "      <td>184.67</td>\n",
       "      <td>16886563</td>\n",
       "      <td>low</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-04</th>\n",
       "      <td>184.90</td>\n",
       "      <td>186.21</td>\n",
       "      <td>184.0996</td>\n",
       "      <td>184.33</td>\n",
       "      <td>13880896</td>\n",
       "      <td>low</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-05</th>\n",
       "      <td>185.59</td>\n",
       "      <td>186.90</td>\n",
       "      <td>184.9300</td>\n",
       "      <td>186.85</td>\n",
       "      <td>13574535</td>\n",
       "      <td>low</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-08</th>\n",
       "      <td>187.20</td>\n",
       "      <td>188.90</td>\n",
       "      <td>186.3300</td>\n",
       "      <td>188.28</td>\n",
       "      <td>17994726</td>\n",
       "      <td>low</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              open    high       low   close    volume trading_volume\n",
       "date                                                                 \n",
       "2018-01-02  177.68  181.58  177.5500  181.42  18151903            low\n",
       "2018-01-03  181.88  184.78  181.3300  184.67  16886563            low\n",
       "2018-01-04  184.90  186.21  184.0996  184.33  13880896            low\n",
       "2018-01-05  185.59  186.90  184.9300  186.85  13574535            low\n",
       "2018-01-08  187.20  188.90  186.3300  188.28  17994726            low"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fb = pd.read_csv('data/fb_2018.csv', index_col='date', parse_dates=True).assign(\n",
    "    trading_volume=lambda x: pd.cut(x.volume, bins=3, labels=['low', 'med', 'high'])\n",
    ")\n",
    "fb.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Before we dive into any calculations, let's make sure pandas won't put things in scientific notation. We will modify how floats are formatted for displaying. The format we will apply is `.2f`, which will provide the float with 2 digits after the decimal point:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.set_option('display.float_format', lambda x: '%.2f' % x)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Summarizing DataFrames\n",
    "We learned about `agg()` in the [2-dataframe_operations.ipynb notebook](https://github.com/stefmolin/Hands-On-Data-Analysis-with-Pandas/blob/master/ch_04/2-dataframe_operations.ipynb) when we learned about window calculations; however, we can call this on the dataframe directly to aggregate its contents into a single series:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "open            171.45\n",
       "high            218.62\n",
       "low             123.02\n",
       "close           171.51\n",
       "volume   6949682394.00\n",
       "dtype: float64"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fb.agg({\n",
    "    'open': np.mean, \n",
    "    'high': np.max, \n",
    "    'low': np.min, \n",
    "    'close': np.mean, \n",
    "    'volume': np.sum\n",
    "})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can use this to find the total snowfall and precipitation recorded in Central Park in 2018:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "datatype\n",
       "SNOW   1007.00\n",
       "PRCP   1665.30\n",
       "dtype: float64"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "weather.query(\n",
    "    'station == \"GHCND:USW00094728\"'\n",
    ").pivot(columns='datatype', values='value')[['SNOW', 'PRCP']].sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This is equivalent to passing `'sum'` to `agg()`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "datatype\n",
       "SNOW   1007.00\n",
       "PRCP   1665.30\n",
       "dtype: float64"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "weather.query(\n",
    "    'station == \"GHCND:USW00094728\"'\n",
    ").pivot(columns='datatype', values='value')[['SNOW', 'PRCP']].agg('sum')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note that we aren't limited to providing a single aggregation per column. We can pass a list, and we will get a dataframe back instead of a series. `nan` values are placed where we don't have a calculation result to display:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>open</th>\n",
       "      <th>high</th>\n",
       "      <th>low</th>\n",
       "      <th>close</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>nan</td>\n",
       "      <td>218.62</td>\n",
       "      <td>214.27</td>\n",
       "      <td>nan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>171.45</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>171.51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>nan</td>\n",
       "      <td>129.74</td>\n",
       "      <td>123.02</td>\n",
       "      <td>nan</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       open   high    low  close\n",
       "max     nan 218.62 214.27    nan\n",
       "mean 171.45    nan    nan 171.51\n",
       "min     nan 129.74 123.02    nan"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fb.agg({\n",
    "    'open': 'mean',\n",
    "    'high': ['min', 'max'],\n",
    "    'low': ['min', 'max'],\n",
    "    'close': 'mean'\n",
    "})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Using `groupby()`\n",
    "Often we won't want to aggregate on the entire dataframe, but on groups within it. For this purpose, we can run `groupby()` before the aggregation. If we group by the `trading_volume` column, we will get a row for each of the values it takes on:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>open</th>\n",
       "      <th>high</th>\n",
       "      <th>low</th>\n",
       "      <th>close</th>\n",
       "      <th>volume</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>trading_volume</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>low</th>\n",
       "      <td>171.36</td>\n",
       "      <td>173.46</td>\n",
       "      <td>169.31</td>\n",
       "      <td>171.43</td>\n",
       "      <td>24547207.71</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>med</th>\n",
       "      <td>175.82</td>\n",
       "      <td>179.42</td>\n",
       "      <td>172.11</td>\n",
       "      <td>175.14</td>\n",
       "      <td>79072559.12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>high</th>\n",
       "      <td>167.73</td>\n",
       "      <td>170.48</td>\n",
       "      <td>161.57</td>\n",
       "      <td>168.16</td>\n",
       "      <td>141924023.33</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 open   high    low  close       volume\n",
       "trading_volume                                         \n",
       "low            171.36 173.46 169.31 171.43  24547207.71\n",
       "med            175.82 179.42 172.11 175.14  79072559.12\n",
       "high           167.73 170.48 161.57 168.16 141924023.33"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fb.groupby('trading_volume').mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "After we run the `groupby()`, we can still select columns for aggregation:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>min</th>\n",
       "      <th>max</th>\n",
       "      <th>mean</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>trading_volume</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>low</th>\n",
       "      <td>124.06</td>\n",
       "      <td>214.67</td>\n",
       "      <td>171.43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>med</th>\n",
       "      <td>152.22</td>\n",
       "      <td>217.50</td>\n",
       "      <td>175.14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>high</th>\n",
       "      <td>160.06</td>\n",
       "      <td>176.26</td>\n",
       "      <td>168.16</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  min    max   mean\n",
       "trading_volume                     \n",
       "low            124.06 214.67 171.43\n",
       "med            152.22 217.50 175.14\n",
       "high           160.06 176.26 168.16"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fb.groupby('trading_volume')['close'].agg(['min', 'max', 'mean'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can still provide a dictionary specifying the aggregations to perform, but passing a list for a column will result in a hierarchical index for the columns:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>open</th>\n",
       "      <th colspan=\"2\" halign=\"left\">high</th>\n",
       "      <th colspan=\"2\" halign=\"left\">low</th>\n",
       "      <th>close</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>mean</th>\n",
       "      <th>min</th>\n",
       "      <th>max</th>\n",
       "      <th>min</th>\n",
       "      <th>max</th>\n",
       "      <th>mean</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>trading_volume</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>low</th>\n",
       "      <td>171.36</td>\n",
       "      <td>129.74</td>\n",
       "      <td>216.20</td>\n",
       "      <td>123.02</td>\n",
       "      <td>212.60</td>\n",
       "      <td>171.43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>med</th>\n",
       "      <td>175.82</td>\n",
       "      <td>162.85</td>\n",
       "      <td>218.62</td>\n",
       "      <td>150.75</td>\n",
       "      <td>214.27</td>\n",
       "      <td>175.14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>high</th>\n",
       "      <td>167.73</td>\n",
       "      <td>161.10</td>\n",
       "      <td>180.13</td>\n",
       "      <td>149.02</td>\n",
       "      <td>173.75</td>\n",
       "      <td>168.16</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 open   high           low         close\n",
       "                 mean    min    max    min    max   mean\n",
       "trading_volume                                          \n",
       "low            171.36 129.74 216.20 123.02 212.60 171.43\n",
       "med            175.82 162.85 218.62 150.75 214.27 175.14\n",
       "high           167.73 161.10 180.13 149.02 173.75 168.16"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fb_agg = fb.groupby('trading_volume').agg({\n",
    "    'open': 'mean',\n",
    "    'high': ['min', 'max'],\n",
    "    'low': ['min', 'max'],\n",
    "    'close': 'mean'\n",
    "})\n",
    "fb_agg"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The hierarchical index in the columns looks like this:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "MultiIndex(levels=[['open', 'high', 'low', 'close'], ['max', 'mean', 'min']],\n",
       "           labels=[[0, 1, 1, 2, 2, 3], [1, 2, 0, 2, 0, 1]])"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fb_agg.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Using a list comprehension, we can join the levels (in a tuple) with an `_` at each iteration: "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>open_mean</th>\n",
       "      <th>high_min</th>\n",
       "      <th>high_max</th>\n",
       "      <th>low_min</th>\n",
       "      <th>low_max</th>\n",
       "      <th>close_mean</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>trading_volume</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>low</th>\n",
       "      <td>171.36</td>\n",
       "      <td>129.74</td>\n",
       "      <td>216.20</td>\n",
       "      <td>123.02</td>\n",
       "      <td>212.60</td>\n",
       "      <td>171.43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>med</th>\n",
       "      <td>175.82</td>\n",
       "      <td>162.85</td>\n",
       "      <td>218.62</td>\n",
       "      <td>150.75</td>\n",
       "      <td>214.27</td>\n",
       "      <td>175.14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>high</th>\n",
       "      <td>167.73</td>\n",
       "      <td>161.10</td>\n",
       "      <td>180.13</td>\n",
       "      <td>149.02</td>\n",
       "      <td>173.75</td>\n",
       "      <td>168.16</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                open_mean  high_min  high_max  low_min  low_max  close_mean\n",
       "trading_volume                                                             \n",
       "low                171.36    129.74    216.20   123.02   212.60      171.43\n",
       "med                175.82    162.85    218.62   150.75   214.27      175.14\n",
       "high               167.73    161.10    180.13   149.02   173.75      168.16"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fb_agg.columns = ['_'.join(col_agg) for col_agg in fb_agg.columns]\n",
    "fb_agg.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can group on datetimes despite them being in the index if we use a `Grouper`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>value</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2018-10-01</th>\n",
       "      <td>0.01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-10-02</th>\n",
       "      <td>2.23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-10-03</th>\n",
       "      <td>19.69</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-10-04</th>\n",
       "      <td>0.32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-10-05</th>\n",
       "      <td>0.96</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            value\n",
       "date             \n",
       "2018-10-01   0.01\n",
       "2018-10-02   2.23\n",
       "2018-10-03  19.69\n",
       "2018-10-04   0.32\n",
       "2018-10-05   0.96"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "weather['2018-10'].query('datatype == \"PRCP\"').groupby(\n",
    "    pd.Grouper(freq='D')\n",
    ").mean().head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This `Grouper` can be one of many group by values. Here, we find the quarterly total precipitation per station:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"4\" halign=\"left\">value</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th>2018-03-31</th>\n",
       "      <th>2018-06-30</th>\n",
       "      <th>2018-09-30</th>\n",
       "      <th>2018-12-31</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>station_name</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>WANTAGH 1.1 NNE, NY US</th>\n",
       "      <td>279.90</td>\n",
       "      <td>216.80</td>\n",
       "      <td>472.50</td>\n",
       "      <td>277.20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>STATEN ISLAND 1.4 SE, NY US</th>\n",
       "      <td>379.40</td>\n",
       "      <td>295.30</td>\n",
       "      <td>438.80</td>\n",
       "      <td>409.90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SYOSSET 2.0 SSW, NY US</th>\n",
       "      <td>323.50</td>\n",
       "      <td>263.30</td>\n",
       "      <td>355.50</td>\n",
       "      <td>459.90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>STAMFORD 4.2 S, CT US</th>\n",
       "      <td>338.00</td>\n",
       "      <td>272.10</td>\n",
       "      <td>424.70</td>\n",
       "      <td>390.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>WAYNE TWP 0.8 SSW, NJ US</th>\n",
       "      <td>246.20</td>\n",
       "      <td>295.30</td>\n",
       "      <td>620.90</td>\n",
       "      <td>422.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                 value                                 \n",
       "date                        2018-03-31 2018-06-30 2018-09-30 2018-12-31\n",
       "station_name                                                           \n",
       "WANTAGH 1.1 NNE, NY US          279.90     216.80     472.50     277.20\n",
       "STATEN ISLAND 1.4 SE, NY US     379.40     295.30     438.80     409.90\n",
       "SYOSSET 2.0 SSW, NY US          323.50     263.30     355.50     459.90\n",
       "STAMFORD 4.2 S, CT US           338.00     272.10     424.70     390.00\n",
       "WAYNE TWP 0.8 SSW, NJ US        246.20     295.30     620.90     422.00"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "weather.query('datatype == \"PRCP\"').groupby(\n",
    "    ['station_name', pd.Grouper(freq='Q')]\n",
    ").sum().unstack().sample(5, random_state=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note that we can use `filter()` to exclude some groups from aggregation. Here, we only keep groups with 'NY' in the group's `name` attribute, which is the station ID in this case:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "station_name\n",
       "ALBERTSON 0.2 SSE, NY US         1087.00\n",
       "AMITYVILLE 0.1 WSW, NY US         434.00\n",
       "AMITYVILLE 0.6 NNE, NY US        1072.00\n",
       "ARMONK 0.3 SE, NY US             1504.00\n",
       "BROOKLYN 3.1 NW, NY US            305.00\n",
       "CENTERPORT 0.9 SW, NY US          799.00\n",
       "ELMSFORD 0.8 SSW, NY US           863.00\n",
       "FLORAL PARK 0.4 W, NY US         1015.00\n",
       "HICKSVILLE 1.3 ENE, NY US         716.00\n",
       "JACKSON HEIGHTS 0.3 WSW, NY US    107.00\n",
       "LOCUST VALLEY 0.3 E, NY US          0.00\n",
       "LYNBROOK 0.3 NW, NY US            325.00\n",
       "MASSAPEQUA 0.9 SSW, NY US          41.00\n",
       "MIDDLE VILLAGE 0.5 SW, NY US     1249.00\n",
       "NEW HYDE PARK 1.6 NE, NY US         0.00\n",
       "NEW YORK 8.8 N, NY US               0.00\n",
       "NORTH WANTAGH 0.4 WSW, NY US      471.00\n",
       "PLAINEDGE 0.4 WSW, NY US          610.00\n",
       "PLAINVIEW 0.4 ENE, NY US         1360.00\n",
       "SADDLE ROCK 3.4 WSW, NY US        707.00\n",
       "STATEN ISLAND 1.4 SE, NY US       936.00\n",
       "STATEN ISLAND 4.5 SSE, NY US       89.00\n",
       "SYOSSET 2.0 SSW, NY US           1039.00\n",
       "VALLEY STREAM 0.6 SE, NY US       898.00\n",
       "WANTAGH 0.3 ESE, NY US           1280.00\n",
       "WANTAGH 1.1 NNE, NY US            940.00\n",
       "WEST NYACK 1.3 WSW, NY US        1371.00\n",
       "Name: value, dtype: float64"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "weather.groupby('station').filter( # station IDs with NY in them\n",
    "    lambda x: 'NY' in x.name \n",
    ").query('datatype == \"SNOW\"').groupby('station_name').sum().squeeze() # aggregate and make a series (squeeze)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's see which months have the most precipitation. First, we need to group by day and average the precipitation across the stations. Then we can group by month and sum the resulting precipitation. We use `nlargest()` to give the 5 months with the most precipitation:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "date\n",
       "2018-11-30   210.59\n",
       "2018-09-30   193.09\n",
       "2018-08-31   192.45\n",
       "2018-07-31   160.98\n",
       "2018-02-28   158.11\n",
       "Name: value, dtype: float64"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "weather.query('datatype == \"PRCP\"').groupby(\n",
    "    pd.Grouper(freq='D')\n",
    ").mean().groupby(pd.Grouper(freq='M')).sum().value.nlargest()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Perhaps the previous result was surprising. The saying goes \"April showers bring May flowers\"; yet April wasn't in the top 5 (neither was May for that matter). Snow will count towards precipitation, but that doesn't explain why summer months are higher than April. Let's look for days that accounted for a large percentage of the precipitation in a given month. \n",
    "\n",
    "In order to do so, we need to calculate the average daily precipitation across stations and then find the total per month. This will be the denominator. However, in order to divide the daily values by the total for their month, we will need a Series of equal dimensions. This means we will need to use `transform()`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>prcp</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2018-01-28</th>\n",
       "      <td>69.31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-29</th>\n",
       "      <td>69.31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-30</th>\n",
       "      <td>69.31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-31</th>\n",
       "      <td>69.31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-01</th>\n",
       "      <td>158.11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-02</th>\n",
       "      <td>158.11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-03</th>\n",
       "      <td>158.11</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             prcp\n",
       "date             \n",
       "2018-01-28  69.31\n",
       "2018-01-29  69.31\n",
       "2018-01-30  69.31\n",
       "2018-01-31  69.31\n",
       "2018-02-01 158.11\n",
       "2018-02-02 158.11\n",
       "2018-02-03 158.11"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "weather.query('datatype == \"PRCP\"').rename(\n",
    "    dict(value='prcp'), axis=1\n",
    ").groupby(pd.Grouper(freq='D')).mean().groupby(\n",
    "    pd.Grouper(freq='M')\n",
    ").transform(np.sum)['2018-01-28':'2018-02-03']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Notice how we have the same value repeated for each day in the month it belongs to. This will allow us to calculate the percentage of the monthly precipitation that occurred each day and then pull out the largest values:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>prcp</th>\n",
       "      <th>total_prcp_in_month</th>\n",
       "      <th>pct_monthly_prcp</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2018-10-12</th>\n",
       "      <td>34.77</td>\n",
       "      <td>105.63</td>\n",
       "      <td>0.33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-13</th>\n",
       "      <td>21.66</td>\n",
       "      <td>69.31</td>\n",
       "      <td>0.31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-03-02</th>\n",
       "      <td>38.77</td>\n",
       "      <td>137.46</td>\n",
       "      <td>0.28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-04-16</th>\n",
       "      <td>39.34</td>\n",
       "      <td>140.57</td>\n",
       "      <td>0.28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-04-17</th>\n",
       "      <td>37.30</td>\n",
       "      <td>140.57</td>\n",
       "      <td>0.27</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            prcp  total_prcp_in_month  pct_monthly_prcp\n",
       "date                                                   \n",
       "2018-10-12 34.77               105.63              0.33\n",
       "2018-01-13 21.66                69.31              0.31\n",
       "2018-03-02 38.77               137.46              0.28\n",
       "2018-04-16 39.34               140.57              0.28\n",
       "2018-04-17 37.30               140.57              0.27"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "weather\\\n",
    "    .query('datatype == \"PRCP\"')\\\n",
    "    .rename(dict(value='prcp'), axis=1)\\\n",
    "    .groupby(pd.Grouper(freq='D')).mean()\\\n",
    "    .assign(\n",
    "        total_prcp_in_month=lambda x: x.groupby(\n",
    "                pd.Grouper(freq='M')\n",
    "        ).transform(np.sum),\n",
    "        pct_monthly_prcp=lambda x: x.prcp.div(\n",
    "            x.total_prcp_in_month\n",
    "        )\n",
    "    ).nlargest(5, 'pct_monthly_prcp')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`transform()` can be used on dataframes as well. We can use it to easily standardize the data:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>open</th>\n",
       "      <th>high</th>\n",
       "      <th>low</th>\n",
       "      <th>close</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2018-01-02</th>\n",
       "      <td>0.32</td>\n",
       "      <td>0.41</td>\n",
       "      <td>0.41</td>\n",
       "      <td>0.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-03</th>\n",
       "      <td>0.53</td>\n",
       "      <td>0.57</td>\n",
       "      <td>0.60</td>\n",
       "      <td>0.66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-04</th>\n",
       "      <td>0.68</td>\n",
       "      <td>0.65</td>\n",
       "      <td>0.74</td>\n",
       "      <td>0.64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-05</th>\n",
       "      <td>0.72</td>\n",
       "      <td>0.68</td>\n",
       "      <td>0.78</td>\n",
       "      <td>0.77</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-08</th>\n",
       "      <td>0.80</td>\n",
       "      <td>0.79</td>\n",
       "      <td>0.85</td>\n",
       "      <td>0.84</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            open  high  low  close\n",
       "date                              \n",
       "2018-01-02  0.32  0.41 0.41   0.50\n",
       "2018-01-03  0.53  0.57 0.60   0.66\n",
       "2018-01-04  0.68  0.65 0.74   0.64\n",
       "2018-01-05  0.72  0.68 0.78   0.77\n",
       "2018-01-08  0.80  0.79 0.85   0.84"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fb[['open', 'high', 'low', 'close']].transform(\n",
    "    lambda x: (x - x.mean()).div(x.std())\n",
    ").head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Pivot tables and crosstabs\n",
    "We saw pivots in [chapter 3](https://github.com/stefmolin/Hands-On-Data-Analysis-with-Pandas/blob/master/ch_03/4-reshaping_data.ipynb); however, we weren't able to provide any aggregations. With `pivot_table()`, we get the mean by default as the `aggfunc`. In its simplest form, we provide a column to place along the columns:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>trading_volume</th>\n",
       "      <th>low</th>\n",
       "      <th>med</th>\n",
       "      <th>high</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>close</th>\n",
       "      <td>171.43</td>\n",
       "      <td>175.14</td>\n",
       "      <td>168.16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>high</th>\n",
       "      <td>173.46</td>\n",
       "      <td>179.42</td>\n",
       "      <td>170.48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>low</th>\n",
       "      <td>169.31</td>\n",
       "      <td>172.11</td>\n",
       "      <td>161.57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>open</th>\n",
       "      <td>171.36</td>\n",
       "      <td>175.82</td>\n",
       "      <td>167.73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>volume</th>\n",
       "      <td>24547207.71</td>\n",
       "      <td>79072559.12</td>\n",
       "      <td>141924023.33</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "trading_volume         low         med         high\n",
       "close               171.43      175.14       168.16\n",
       "high                173.46      179.42       170.48\n",
       "low                 169.31      172.11       161.57\n",
       "open                171.36      175.82       167.73\n",
       "volume         24547207.71 79072559.12 141924023.33"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fb.pivot_table(columns='trading_volume')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "By placing the trading volume in the index, we get the aggregation from the first example in the group by section above:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>close</th>\n",
       "      <th>high</th>\n",
       "      <th>low</th>\n",
       "      <th>open</th>\n",
       "      <th>volume</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>trading_volume</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>low</th>\n",
       "      <td>171.43</td>\n",
       "      <td>173.46</td>\n",
       "      <td>169.31</td>\n",
       "      <td>171.36</td>\n",
       "      <td>24547207.71</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>med</th>\n",
       "      <td>175.14</td>\n",
       "      <td>179.42</td>\n",
       "      <td>172.11</td>\n",
       "      <td>175.82</td>\n",
       "      <td>79072559.12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>high</th>\n",
       "      <td>168.16</td>\n",
       "      <td>170.48</td>\n",
       "      <td>161.57</td>\n",
       "      <td>167.73</td>\n",
       "      <td>141924023.33</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                close   high    low   open       volume\n",
       "trading_volume                                         \n",
       "low            171.43 173.46 169.31 171.36  24547207.71\n",
       "med            175.14 179.42 172.11 175.82  79072559.12\n",
       "high           168.16 170.48 161.57 167.73 141924023.33"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fb.pivot_table(index='trading_volume')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "With `pivot()`, we also weren't able to handle multi-level indices or indices with repeated values. For this reason we haven't been able to put the weather data in the wide format. The `pivot_table()` method solves this issue:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>datatype</th>\n",
       "      <th>date</th>\n",
       "      <th>station</th>\n",
       "      <th>station_name</th>\n",
       "      <th>AWND</th>\n",
       "      <th>DAPR</th>\n",
       "      <th>MDPR</th>\n",
       "      <th>PGTM</th>\n",
       "      <th>PRCP</th>\n",
       "      <th>SNOW</th>\n",
       "      <th>SNWD</th>\n",
       "      <th>...</th>\n",
       "      <th>WSF5</th>\n",
       "      <th>WT01</th>\n",
       "      <th>WT02</th>\n",
       "      <th>WT03</th>\n",
       "      <th>WT04</th>\n",
       "      <th>WT05</th>\n",
       "      <th>WT06</th>\n",
       "      <th>WT08</th>\n",
       "      <th>WT09</th>\n",
       "      <th>WT11</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>28740</th>\n",
       "      <td>2018-12-31</td>\n",
       "      <td>GHCND:USW00054787</td>\n",
       "      <td>FARMINGDALE REPUBLIC AIRPORT, NY US</td>\n",
       "      <td>5.00</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>2052.00</td>\n",
       "      <td>28.70</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>...</td>\n",
       "      <td>15.70</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28741</th>\n",
       "      <td>2018-12-31</td>\n",
       "      <td>GHCND:USW00094728</td>\n",
       "      <td>NY CITY CENTRAL PARK, NY US</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>25.90</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>...</td>\n",
       "      <td>nan</td>\n",
       "      <td>1.00</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28742</th>\n",
       "      <td>2018-12-31</td>\n",
       "      <td>GHCND:USW00094741</td>\n",
       "      <td>TETERBORO AIRPORT, NJ US</td>\n",
       "      <td>1.70</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>1954.00</td>\n",
       "      <td>29.20</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>...</td>\n",
       "      <td>8.90</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28743</th>\n",
       "      <td>2018-12-31</td>\n",
       "      <td>GHCND:USW00094745</td>\n",
       "      <td>WESTCHESTER CO AIRPORT, NY US</td>\n",
       "      <td>2.70</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>2212.00</td>\n",
       "      <td>24.40</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>...</td>\n",
       "      <td>11.20</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28744</th>\n",
       "      <td>2018-12-31</td>\n",
       "      <td>GHCND:USW00094789</td>\n",
       "      <td>JFK INTERNATIONAL AIRPORT, NY US</td>\n",
       "      <td>4.10</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>31.20</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>...</td>\n",
       "      <td>12.50</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 30 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "datatype       date            station                         station_name  \\\n",
       "28740    2018-12-31  GHCND:USW00054787  FARMINGDALE REPUBLIC AIRPORT, NY US   \n",
       "28741    2018-12-31  GHCND:USW00094728          NY CITY CENTRAL PARK, NY US   \n",
       "28742    2018-12-31  GHCND:USW00094741             TETERBORO AIRPORT, NJ US   \n",
       "28743    2018-12-31  GHCND:USW00094745        WESTCHESTER CO AIRPORT, NY US   \n",
       "28744    2018-12-31  GHCND:USW00094789     JFK INTERNATIONAL AIRPORT, NY US   \n",
       "\n",
       "datatype  AWND  DAPR  MDPR    PGTM  PRCP  SNOW  SNWD  ...   WSF5  WT01  WT02  \\\n",
       "28740     5.00   nan   nan 2052.00 28.70   nan   nan  ...  15.70   nan   nan   \n",
       "28741      nan   nan   nan     nan 25.90  0.00  0.00  ...    nan  1.00   nan   \n",
       "28742     1.70   nan   nan 1954.00 29.20   nan   nan  ...   8.90   nan   nan   \n",
       "28743     2.70   nan   nan 2212.00 24.40   nan   nan  ...  11.20   nan   nan   \n",
       "28744     4.10   nan   nan     nan 31.20  0.00  0.00  ...  12.50  1.00  1.00   \n",
       "\n",
       "datatype  WT03  WT04  WT05  WT06  WT08  WT09  WT11  \n",
       "28740      nan   nan   nan   nan   nan   nan   nan  \n",
       "28741      nan   nan   nan   nan   nan   nan   nan  \n",
       "28742      nan   nan   nan   nan   nan   nan   nan  \n",
       "28743      nan   nan   nan   nan   nan   nan   nan  \n",
       "28744      nan   nan   nan   nan   nan   nan   nan  \n",
       "\n",
       "[5 rows x 30 columns]"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "weather.reset_index().pivot_table(\n",
    "    index=['date', 'station', 'station_name'], \n",
    "    columns='datatype', \n",
    "    values='value',\n",
    "    aggfunc='median'\n",
    ").reset_index().tail()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can use the `pd.crosstab()` function to create a frequency table. For example, if we want to see how many low-, medium-, and high-volume trading days Facebook stock had each month, we can use crosstab:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>month</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "      <th>6</th>\n",
       "      <th>7</th>\n",
       "      <th>8</th>\n",
       "      <th>9</th>\n",
       "      <th>10</th>\n",
       "      <th>11</th>\n",
       "      <th>12</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>trading_volume</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>low</th>\n",
       "      <td>20</td>\n",
       "      <td>19</td>\n",
       "      <td>15</td>\n",
       "      <td>20</td>\n",
       "      <td>22</td>\n",
       "      <td>21</td>\n",
       "      <td>18</td>\n",
       "      <td>23</td>\n",
       "      <td>19</td>\n",
       "      <td>23</td>\n",
       "      <td>21</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>med</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>high</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "month           1   2   3   4   5   6   7   8   9   10  11  12\n",
       "trading_volume                                                \n",
       "low             20  19  15  20  22  21  18  23  19  23  21  19\n",
       "med              1   0   4   1   0   0   2   0   0   0   0   0\n",
       "high             0   0   2   0   0   0   1   0   0   0   0   0"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.crosstab(\n",
    "    index=fb.trading_volume,\n",
    "    columns=fb.index.month,\n",
    "    colnames=['month'] # name the columns index\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can normalize with the row or column totals with the `normalize` parameter. This shows percentage of the total:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>month</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "      <th>6</th>\n",
       "      <th>7</th>\n",
       "      <th>8</th>\n",
       "      <th>9</th>\n",
       "      <th>10</th>\n",
       "      <th>11</th>\n",
       "      <th>12</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>trading_volume</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>low</th>\n",
       "      <td>0.95</td>\n",
       "      <td>1.00</td>\n",
       "      <td>0.71</td>\n",
       "      <td>0.95</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>0.86</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>med</th>\n",
       "      <td>0.05</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.19</td>\n",
       "      <td>0.05</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.10</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>high</th>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.10</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.05</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "month            1    2    3    4    5    6    7    8    9    10   11   12\n",
       "trading_volume                                                            \n",
       "low            0.95 1.00 0.71 0.95 1.00 1.00 0.86 1.00 1.00 1.00 1.00 1.00\n",
       "med            0.05 0.00 0.19 0.05 0.00 0.00 0.10 0.00 0.00 0.00 0.00 0.00\n",
       "high           0.00 0.00 0.10 0.00 0.00 0.00 0.05 0.00 0.00 0.00 0.00 0.00"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.crosstab(\n",
    "    index=fb.trading_volume,\n",
    "    columns=fb.index.month,\n",
    "    colnames=['month'],\n",
    "    normalize='columns'\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If we want to perform a calculation other than counting the frequency, we can pass the column to run the calculation on to `values` and the function to use to `aggfunc`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>month</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "      <th>6</th>\n",
       "      <th>7</th>\n",
       "      <th>8</th>\n",
       "      <th>9</th>\n",
       "      <th>10</th>\n",
       "      <th>11</th>\n",
       "      <th>12</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>trading_volume</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>low</th>\n",
       "      <td>185.24</td>\n",
       "      <td>180.27</td>\n",
       "      <td>177.07</td>\n",
       "      <td>163.29</td>\n",
       "      <td>182.93</td>\n",
       "      <td>195.27</td>\n",
       "      <td>201.92</td>\n",
       "      <td>177.49</td>\n",
       "      <td>164.38</td>\n",
       "      <td>154.19</td>\n",
       "      <td>141.64</td>\n",
       "      <td>137.16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>med</th>\n",
       "      <td>179.37</td>\n",
       "      <td>nan</td>\n",
       "      <td>164.76</td>\n",
       "      <td>174.16</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>194.28</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>high</th>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>164.11</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>176.26</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "month              1      2      3      4      5      6      7      8      9   \\\n",
       "trading_volume                                                                  \n",
       "low            185.24 180.27 177.07 163.29 182.93 195.27 201.92 177.49 164.38   \n",
       "med            179.37    nan 164.76 174.16    nan    nan 194.28    nan    nan   \n",
       "high              nan    nan 164.11    nan    nan    nan 176.26    nan    nan   \n",
       "\n",
       "month              10     11     12  \n",
       "trading_volume                       \n",
       "low            154.19 141.64 137.16  \n",
       "med               nan    nan    nan  \n",
       "high              nan    nan    nan  "
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.crosstab(\n",
    "    index=fb.trading_volume,\n",
    "    columns=fb.index.month,\n",
    "    colnames=['month'],\n",
    "    values=fb.close,\n",
    "    aggfunc=np.mean\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can also get row and column subtotals with the `margins` parameter. Let's count the number of times each station recorded snow per month and include the subtotals:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>month</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "      <th>6</th>\n",
       "      <th>7</th>\n",
       "      <th>8</th>\n",
       "      <th>9</th>\n",
       "      <th>10</th>\n",
       "      <th>11</th>\n",
       "      <th>12</th>\n",
       "      <th>total observations of snow</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>station_name</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>ALBERTSON 0.2 SSE, NY US</th>\n",
       "      <td>3.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>3.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>9.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AMITYVILLE 0.1 WSW, NY US</th>\n",
       "      <td>1.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>3.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AMITYVILLE 0.6 NNE, NY US</th>\n",
       "      <td>3.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>3.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>8.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ARMONK 0.3 SE, NY US</th>\n",
       "      <td>6.00</td>\n",
       "      <td>4.00</td>\n",
       "      <td>6.00</td>\n",
       "      <td>3.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>3.00</td>\n",
       "      <td>23.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BLOOMINGDALE 0.7 SSE, NJ US</th>\n",
       "      <td>2.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>3.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>8.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BOONTON 0.6 NW, NJ US</th>\n",
       "      <td>4.00</td>\n",
       "      <td>2.00</td>\n",
       "      <td>2.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>11.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BOONTON 0.7 WSW, NJ US</th>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>0.00</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>nan</td>\n",
       "      <td>0.00</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BOONTON 1 SE, NJ US</th>\n",
       "      <td>3.00</td>\n",
       "      <td>2.00</td>\n",
       "      <td>3.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>9.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BROOKLYN 3.1 NW, NY US</th>\n",
       "      <td>2.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>2.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>5.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CANOE BROOK, NJ US</th>\n",
       "      <td>2.00</td>\n",
       "      <td>nan</td>\n",
       "      <td>2.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>6.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CARTERET 0.6 WSW, NJ US</th>\n",
       "      <td>1.00</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>1.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CEDAR GROVE TWP 0.4 W, NJ US</th>\n",
       "      <td>4.00</td>\n",
       "      <td>2.00</td>\n",
       "      <td>5.00</td>\n",
       "      <td>2.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>2.00</td>\n",
       "      <td>2.00</td>\n",
       "      <td>17.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CENTERPORT 0.9 SW, NY US</th>\n",
       "      <td>2.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>4.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>8.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CENTERPORT, NY US</th>\n",
       "      <td>5.00</td>\n",
       "      <td>3.00</td>\n",
       "      <td>6.00</td>\n",
       "      <td>2.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>17.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CHATHAM 0.6 NW, NJ US</th>\n",
       "      <td>3.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>4.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>9.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CHATHAM TWP 1.1 NNW, NJ US</th>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>nan</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CHATHAM TWP 2.0 NNW, NJ US</th>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>nan</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>COLTS NECK TWP 2.4 NW, NJ US</th>\n",
       "      <td>1.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CRANFORD TWP 1.1 NNW, NJ US</th>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EAST BRUNSWICK TWP 3.3 NNE, NJ US</th>\n",
       "      <td>nan</td>\n",
       "      <td>1.00</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>1.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EATONTOWN 1.2 NE, NJ US</th>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EDISON TWP 1.9 N, NJ US</th>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ELMSFORD 0.8 SSW, NY US</th>\n",
       "      <td>3.00</td>\n",
       "      <td>3.00</td>\n",
       "      <td>2.00</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>8.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>FLORAL PARK 0.4 W, NY US</th>\n",
       "      <td>3.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>3.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>8.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>FLORHAM PARK 0.2 WNW, NJ US</th>\n",
       "      <td>6.00</td>\n",
       "      <td>2.00</td>\n",
       "      <td>4.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>14.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GLEN ROCK 0.4 WNW, NJ US</th>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GLEN ROCK 0.7 SSE, NJ US</th>\n",
       "      <td>6.00</td>\n",
       "      <td>2.00</td>\n",
       "      <td>4.00</td>\n",
       "      <td>2.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>15.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>HARRISON 0.3 N, NJ US</th>\n",
       "      <td>4.00</td>\n",
       "      <td>2.00</td>\n",
       "      <td>4.00</td>\n",
       "      <td>2.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>13.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>HARRISON, NJ US</th>\n",
       "      <td>4.00</td>\n",
       "      <td>2.00</td>\n",
       "      <td>4.00</td>\n",
       "      <td>2.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>13.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>HAWTHORNE 0.4 S, NJ US</th>\n",
       "      <td>4.00</td>\n",
       "      <td>2.00</td>\n",
       "      <td>4.00</td>\n",
       "      <td>2.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>2.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>14.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PARSIPPANY TROY HILLS TWP 1.5, NJ US</th>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>2.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PLAINEDGE 0.4 WSW, NY US</th>\n",
       "      <td>1.00</td>\n",
       "      <td>nan</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>3.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PLAINVIEW 0.4 ENE, NY US</th>\n",
       "      <td>3.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>5.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>10.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PUTNAM LAKE, CT US</th>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>nan</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>RED BANK 0.6 ENE, NJ US</th>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>RINGWOOD 3.0 SSE, NJ US</th>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>RIVER EDGE 0.4 NNE, NJ US</th>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SADDLE ROCK 3.4 WSW, NY US</th>\n",
       "      <td>3.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>nan</td>\n",
       "      <td>7.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SOUTH PLAINFIELD 0.7 NNE, NJ US</th>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>1.00</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>1.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SPRINGFIELD TWP 0.7 NNE, NJ US</th>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>STAMFORD 4.2 S, CT US</th>\n",
       "      <td>1.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>0.00</td>\n",
       "      <td>4.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>STATEN ISLAND 1.4 SE, NY US</th>\n",
       "      <td>4.00</td>\n",
       "      <td>2.00</td>\n",
       "      <td>5.00</td>\n",
       "      <td>2.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>14.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>STATEN ISLAND 4.5 SSE, NY US</th>\n",
       "      <td>0.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>2.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SYOSSET 2.0 SSW, NY US</th>\n",
       "      <td>2.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>4.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>9.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SYOSSET, NY US</th>\n",
       "      <td>3.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>6.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TENAFLY 1.3 W, NJ US</th>\n",
       "      <td>1.00</td>\n",
       "      <td>2.00</td>\n",
       "      <td>7.00</td>\n",
       "      <td>2.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>nan</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>2.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>15.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TENAFLY 1.6 NW, NJ US</th>\n",
       "      <td>nan</td>\n",
       "      <td>1.00</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>1.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TETERBORO AIRPORT, NJ US</th>\n",
       "      <td>nan</td>\n",
       "      <td>3.00</td>\n",
       "      <td>4.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>1.00</td>\n",
       "      <td>nan</td>\n",
       "      <td>9.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>VALLEY STREAM 0.6 SE, NY US</th>\n",
       "      <td>2.00</td>\n",
       "      <td>2.00</td>\n",
       "      <td>4.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>9.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>WANAQUE RAYMOND DAM, NJ US</th>\n",
       "      <td>1.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>4.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>8.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>WANTAGH 0.3 ESE, NY US</th>\n",
       "      <td>3.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>4.00</td>\n",
       "      <td>2.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>11.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>WANTAGH 1.1 NNE, NY US</th>\n",
       "      <td>2.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>4.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>nan</td>\n",
       "      <td>9.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>WAYNE TWP 0.8 SSW, NJ US</th>\n",
       "      <td>nan</td>\n",
       "      <td>1.00</td>\n",
       "      <td>2.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>1.00</td>\n",
       "      <td>nan</td>\n",
       "      <td>5.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>WEST CALDWELL TWP 1.3 NE, NJ US</th>\n",
       "      <td>0.00</td>\n",
       "      <td>3.00</td>\n",
       "      <td>4.00</td>\n",
       "      <td>2.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>10.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>WEST NYACK 1.3 WSW, NY US</th>\n",
       "      <td>3.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>5.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>1.00</td>\n",
       "      <td>nan</td>\n",
       "      <td>11.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>WESTFIELD 0.6 NE, NJ US</th>\n",
       "      <td>3.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>4.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>nan</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>nan</td>\n",
       "      <td>1.00</td>\n",
       "      <td>nan</td>\n",
       "      <td>9.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>WOODBRIDGE TWP 1.1 ESE, NJ US</th>\n",
       "      <td>4.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>3.00</td>\n",
       "      <td>2.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>11.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>WOODBRIDGE TWP 1.1 NNE, NJ US</th>\n",
       "      <td>2.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>3.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>7.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>WOODBRIDGE TWP 3.0 NNW, NJ US</th>\n",
       "      <td>nan</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>0.00</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>nan</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>total observations of snow</th>\n",
       "      <td>190.00</td>\n",
       "      <td>97.00</td>\n",
       "      <td>237.00</td>\n",
       "      <td>81.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>49.00</td>\n",
       "      <td>13.00</td>\n",
       "      <td>667.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>99 rows × 13 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "month                                     1     2      3     4    5    6    7  \\\n",
       "station_name                                                                    \n",
       "ALBERTSON 0.2 SSE, NY US               3.00  1.00   3.00  1.00 0.00 0.00 0.00   \n",
       "AMITYVILLE 0.1 WSW, NY US              1.00  0.00   1.00  1.00 0.00 0.00 0.00   \n",
       "AMITYVILLE 0.6 NNE, NY US              3.00  1.00   3.00  1.00 0.00 0.00 0.00   \n",
       "ARMONK 0.3 SE, NY US                   6.00  4.00   6.00  3.00 0.00 0.00 0.00   \n",
       "BLOOMINGDALE 0.7 SSE, NJ US            2.00  1.00   3.00  1.00 0.00 0.00 0.00   \n",
       "BOONTON 0.6 NW, NJ US                  4.00  2.00   2.00  1.00 0.00 0.00 0.00   \n",
       "BOONTON 0.7 WSW, NJ US                  nan   nan    nan  0.00  nan  nan 0.00   \n",
       "BOONTON 1 SE, NJ US                    3.00  2.00   3.00  1.00 0.00 0.00 0.00   \n",
       "BROOKLYN 3.1 NW, NY US                 2.00  0.00   2.00  1.00 0.00 0.00 0.00   \n",
       "CANOE BROOK, NJ US                     2.00   nan   2.00  1.00 0.00 0.00 0.00   \n",
       "CARTERET 0.6 WSW, NJ US                1.00   nan    nan   nan  nan  nan  nan   \n",
       "CEDAR GROVE TWP 0.4 W, NJ US           4.00  2.00   5.00  2.00 0.00 0.00 0.00   \n",
       "CENTERPORT 0.9 SW, NY US               2.00  1.00   4.00  1.00 0.00 0.00 0.00   \n",
       "CENTERPORT, NY US                      5.00  3.00   6.00  2.00 0.00 0.00 0.00   \n",
       "CHATHAM 0.6 NW, NJ US                  3.00  1.00   4.00  1.00 0.00 0.00 0.00   \n",
       "CHATHAM TWP 1.1 NNW, NJ US              nan   nan    nan  0.00 0.00 0.00 0.00   \n",
       "CHATHAM TWP 2.0 NNW, NJ US              nan   nan    nan  0.00 0.00 0.00 0.00   \n",
       "COLTS NECK TWP 2.4 NW, NJ US           1.00  0.00   0.00  0.00 0.00 0.00 0.00   \n",
       "CRANFORD TWP 1.1 NNW, NJ US            0.00  0.00   0.00  0.00 0.00 0.00 0.00   \n",
       "EAST BRUNSWICK TWP 3.3 NNE, NJ US       nan  1.00    nan   nan  nan  nan  nan   \n",
       "EATONTOWN 1.2 NE, NJ US                0.00  0.00   1.00  0.00 0.00 0.00 0.00   \n",
       "EDISON TWP 1.9 N, NJ US                 nan   nan    nan  0.00 0.00 0.00 0.00   \n",
       "ELMSFORD 0.8 SSW, NY US                3.00  3.00   2.00   nan  nan  nan  nan   \n",
       "FLORAL PARK 0.4 W, NY US               3.00  1.00   3.00  1.00 0.00 0.00 0.00   \n",
       "FLORHAM PARK 0.2 WNW, NJ US            6.00  2.00   4.00  1.00 0.00 0.00 0.00   \n",
       "GLEN ROCK 0.4 WNW, NJ US                nan   nan    nan   nan  nan  nan  nan   \n",
       "GLEN ROCK 0.7 SSE, NJ US               6.00  2.00   4.00  2.00 0.00 0.00 0.00   \n",
       "HARRISON 0.3 N, NJ US                  4.00  2.00   4.00  2.00 0.00 0.00 0.00   \n",
       "HARRISON, NJ US                        4.00  2.00   4.00  2.00 0.00 0.00 0.00   \n",
       "HAWTHORNE 0.4 S, NJ US                 4.00  2.00   4.00  2.00 0.00 0.00 0.00   \n",
       "...                                     ...   ...    ...   ...  ...  ...  ...   \n",
       "PARSIPPANY TROY HILLS TWP 1.5, NJ US    nan   nan    nan   nan  nan  nan  nan   \n",
       "PLAINEDGE 0.4 WSW, NY US               1.00   nan   1.00  1.00  nan  nan  nan   \n",
       "PLAINVIEW 0.4 ENE, NY US               3.00  1.00   5.00  1.00 0.00 0.00 0.00   \n",
       "PUTNAM LAKE, CT US                      nan   nan    nan   nan  nan  nan  nan   \n",
       "RED BANK 0.6 ENE, NJ US                 nan   nan    nan   nan  nan  nan  nan   \n",
       "RINGWOOD 3.0 SSE, NJ US                0.00  0.00   0.00  0.00 0.00 0.00 0.00   \n",
       "RIVER EDGE 0.4 NNE, NJ US              0.00  0.00   0.00  0.00 0.00 0.00 0.00   \n",
       "SADDLE ROCK 3.4 WSW, NY US             3.00  1.00   1.00  1.00 0.00 0.00 0.00   \n",
       "SOUTH PLAINFIELD 0.7 NNE, NJ US         nan   nan   1.00   nan  nan  nan  nan   \n",
       "SPRINGFIELD TWP 0.7 NNE, NJ US         0.00  0.00   0.00  0.00 0.00 0.00 0.00   \n",
       "STAMFORD 4.2 S, CT US                  1.00  1.00   1.00  1.00  nan  nan  nan   \n",
       "STATEN ISLAND 1.4 SE, NY US            4.00  2.00   5.00  2.00 0.00 0.00 0.00   \n",
       "STATEN ISLAND 4.5 SSE, NY US           0.00  1.00   0.00  1.00 0.00 0.00 0.00   \n",
       "SYOSSET 2.0 SSW, NY US                 2.00  1.00   4.00  1.00 0.00 0.00 0.00   \n",
       "SYOSSET, NY US                         3.00  1.00   1.00  0.00 0.00 0.00 0.00   \n",
       "TENAFLY 1.3 W, NJ US                   1.00  2.00   7.00  2.00 0.00 0.00  nan   \n",
       "TENAFLY 1.6 NW, NJ US                   nan  1.00    nan   nan  nan  nan  nan   \n",
       "TETERBORO AIRPORT, NJ US                nan  3.00   4.00  1.00  nan  nan  nan   \n",
       "VALLEY STREAM 0.6 SE, NY US            2.00  2.00   4.00  1.00 0.00 0.00 0.00   \n",
       "WANAQUE RAYMOND DAM, NJ US             1.00  1.00   4.00  1.00 0.00 0.00 0.00   \n",
       "WANTAGH 0.3 ESE, NY US                 3.00  1.00   4.00  2.00 0.00 0.00 0.00   \n",
       "WANTAGH 1.1 NNE, NY US                 2.00  1.00   4.00  1.00 0.00 0.00 0.00   \n",
       "WAYNE TWP 0.8 SSW, NJ US                nan  1.00   2.00  1.00  nan  nan  nan   \n",
       "WEST CALDWELL TWP 1.3 NE, NJ US        0.00  3.00   4.00  2.00 0.00 0.00 0.00   \n",
       "WEST NYACK 1.3 WSW, NY US              3.00  1.00   5.00  1.00  nan  nan  nan   \n",
       "WESTFIELD 0.6 NE, NJ US                3.00  0.00   4.00  1.00 0.00  nan 0.00   \n",
       "WOODBRIDGE TWP 1.1 ESE, NJ US          4.00  1.00   3.00  2.00 0.00 0.00 0.00   \n",
       "WOODBRIDGE TWP 1.1 NNE, NJ US          2.00  1.00   3.00  0.00 0.00 0.00 0.00   \n",
       "WOODBRIDGE TWP 3.0 NNW, NJ US           nan  0.00   0.00   nan  nan 0.00  nan   \n",
       "total observations of snow           190.00 97.00 237.00 81.00 0.00 0.00 0.00   \n",
       "\n",
       "month                                   8    9   10    11    12  \\\n",
       "station_name                                                      \n",
       "ALBERTSON 0.2 SSE, NY US             0.00 0.00 0.00  1.00  0.00   \n",
       "AMITYVILLE 0.1 WSW, NY US            0.00 0.00 0.00  0.00  0.00   \n",
       "AMITYVILLE 0.6 NNE, NY US            0.00 0.00 0.00  0.00  0.00   \n",
       "ARMONK 0.3 SE, NY US                 0.00 0.00 0.00  1.00  3.00   \n",
       "BLOOMINGDALE 0.7 SSE, NJ US          0.00 0.00 0.00  0.00  1.00   \n",
       "BOONTON 0.6 NW, NJ US                0.00 0.00 0.00  1.00  1.00   \n",
       "BOONTON 0.7 WSW, NJ US               0.00  nan 0.00   nan   nan   \n",
       "BOONTON 1 SE, NJ US                  0.00 0.00 0.00  0.00  0.00   \n",
       "BROOKLYN 3.1 NW, NY US               0.00 0.00 0.00  0.00  0.00   \n",
       "CANOE BROOK, NJ US                   0.00 0.00 0.00  1.00  0.00   \n",
       "CARTERET 0.6 WSW, NJ US               nan  nan  nan   nan   nan   \n",
       "CEDAR GROVE TWP 0.4 W, NJ US         0.00 0.00 0.00  2.00  2.00   \n",
       "CENTERPORT 0.9 SW, NY US             0.00 0.00 0.00  0.00  0.00   \n",
       "CENTERPORT, NY US                    0.00 0.00 0.00  1.00  0.00   \n",
       "CHATHAM 0.6 NW, NJ US                0.00 0.00 0.00  0.00  0.00   \n",
       "CHATHAM TWP 1.1 NNW, NJ US           0.00 0.00 0.00  0.00   nan   \n",
       "CHATHAM TWP 2.0 NNW, NJ US           0.00 0.00 0.00  0.00   nan   \n",
       "COLTS NECK TWP 2.4 NW, NJ US         0.00 0.00 0.00  0.00  0.00   \n",
       "CRANFORD TWP 1.1 NNW, NJ US          0.00 0.00 0.00  0.00  0.00   \n",
       "EAST BRUNSWICK TWP 3.3 NNE, NJ US     nan  nan  nan   nan   nan   \n",
       "EATONTOWN 1.2 NE, NJ US              0.00 0.00 0.00  0.00  0.00   \n",
       "EDISON TWP 1.9 N, NJ US              0.00 0.00 0.00  0.00  0.00   \n",
       "ELMSFORD 0.8 SSW, NY US               nan  nan  nan   nan   nan   \n",
       "FLORAL PARK 0.4 W, NY US             0.00 0.00 0.00  0.00  0.00   \n",
       "FLORHAM PARK 0.2 WNW, NJ US          0.00 0.00 0.00  1.00  0.00   \n",
       "GLEN ROCK 0.4 WNW, NJ US             0.00 0.00 0.00   nan   nan   \n",
       "GLEN ROCK 0.7 SSE, NJ US             0.00 0.00 0.00  1.00  0.00   \n",
       "HARRISON 0.3 N, NJ US                0.00 0.00 0.00  1.00  0.00   \n",
       "HARRISON, NJ US                      0.00 0.00 0.00  1.00  0.00   \n",
       "HAWTHORNE 0.4 S, NJ US               0.00 0.00 0.00  2.00  0.00   \n",
       "...                                   ...  ...  ...   ...   ...   \n",
       "PARSIPPANY TROY HILLS TWP 1.5, NJ US  nan  nan  nan  1.00  1.00   \n",
       "PLAINEDGE 0.4 WSW, NY US              nan  nan  nan   nan   nan   \n",
       "PLAINVIEW 0.4 ENE, NY US             0.00 0.00 0.00  0.00  0.00   \n",
       "PUTNAM LAKE, CT US                    nan 0.00 0.00  0.00   nan   \n",
       "RED BANK 0.6 ENE, NJ US               nan  nan  nan  0.00  0.00   \n",
       "RINGWOOD 3.0 SSE, NJ US              0.00 0.00 0.00  1.00  0.00   \n",
       "RIVER EDGE 0.4 NNE, NJ US             nan  nan 0.00  0.00  0.00   \n",
       "SADDLE ROCK 3.4 WSW, NY US           0.00 0.00 0.00  1.00   nan   \n",
       "SOUTH PLAINFIELD 0.7 NNE, NJ US       nan  nan  nan   nan   nan   \n",
       "SPRINGFIELD TWP 0.7 NNE, NJ US       0.00 0.00 0.00  0.00  0.00   \n",
       "STAMFORD 4.2 S, CT US                 nan  nan  nan   nan  0.00   \n",
       "STATEN ISLAND 1.4 SE, NY US          0.00 0.00 0.00  1.00  0.00   \n",
       "STATEN ISLAND 4.5 SSE, NY US         0.00 0.00 0.00  0.00  0.00   \n",
       "SYOSSET 2.0 SSW, NY US               0.00 0.00 0.00  1.00  0.00   \n",
       "SYOSSET, NY US                       0.00 0.00 0.00  1.00  0.00   \n",
       "TENAFLY 1.3 W, NJ US                 0.00 0.00 0.00  2.00  1.00   \n",
       "TENAFLY 1.6 NW, NJ US                 nan  nan  nan   nan   nan   \n",
       "TETERBORO AIRPORT, NJ US              nan  nan  nan  1.00   nan   \n",
       "VALLEY STREAM 0.6 SE, NY US           nan  nan  nan   nan   nan   \n",
       "WANAQUE RAYMOND DAM, NJ US           0.00 0.00 0.00  1.00  0.00   \n",
       "WANTAGH 0.3 ESE, NY US               0.00 0.00 0.00  1.00  0.00   \n",
       "WANTAGH 1.1 NNE, NY US               0.00 0.00 0.00  1.00   nan   \n",
       "WAYNE TWP 0.8 SSW, NJ US              nan  nan  nan  1.00   nan   \n",
       "WEST CALDWELL TWP 1.3 NE, NJ US      0.00 0.00 0.00  1.00  0.00   \n",
       "WEST NYACK 1.3 WSW, NY US             nan  nan  nan  1.00   nan   \n",
       "WESTFIELD 0.6 NE, NJ US              0.00 0.00  nan  1.00   nan   \n",
       "WOODBRIDGE TWP 1.1 ESE, NJ US        0.00 0.00 0.00  1.00  0.00   \n",
       "WOODBRIDGE TWP 1.1 NNE, NJ US        0.00 0.00 0.00  1.00  0.00   \n",
       "WOODBRIDGE TWP 3.0 NNW, NJ US         nan  nan 0.00  0.00   nan   \n",
       "total observations of snow           0.00 0.00 0.00 49.00 13.00   \n",
       "\n",
       "month                                 total observations of snow  \n",
       "station_name                                                      \n",
       "ALBERTSON 0.2 SSE, NY US                                    9.00  \n",
       "AMITYVILLE 0.1 WSW, NY US                                   3.00  \n",
       "AMITYVILLE 0.6 NNE, NY US                                   8.00  \n",
       "ARMONK 0.3 SE, NY US                                       23.00  \n",
       "BLOOMINGDALE 0.7 SSE, NJ US                                 8.00  \n",
       "BOONTON 0.6 NW, NJ US                                      11.00  \n",
       "BOONTON 0.7 WSW, NJ US                                      0.00  \n",
       "BOONTON 1 SE, NJ US                                         9.00  \n",
       "BROOKLYN 3.1 NW, NY US                                      5.00  \n",
       "CANOE BROOK, NJ US                                          6.00  \n",
       "CARTERET 0.6 WSW, NJ US                                     1.00  \n",
       "CEDAR GROVE TWP 0.4 W, NJ US                               17.00  \n",
       "CENTERPORT 0.9 SW, NY US                                    8.00  \n",
       "CENTERPORT, NY US                                          17.00  \n",
       "CHATHAM 0.6 NW, NJ US                                       9.00  \n",
       "CHATHAM TWP 1.1 NNW, NJ US                                  0.00  \n",
       "CHATHAM TWP 2.0 NNW, NJ US                                  0.00  \n",
       "COLTS NECK TWP 2.4 NW, NJ US                                1.00  \n",
       "CRANFORD TWP 1.1 NNW, NJ US                                 0.00  \n",
       "EAST BRUNSWICK TWP 3.3 NNE, NJ US                           1.00  \n",
       "EATONTOWN 1.2 NE, NJ US                                     1.00  \n",
       "EDISON TWP 1.9 N, NJ US                                     0.00  \n",
       "ELMSFORD 0.8 SSW, NY US                                     8.00  \n",
       "FLORAL PARK 0.4 W, NY US                                    8.00  \n",
       "FLORHAM PARK 0.2 WNW, NJ US                                14.00  \n",
       "GLEN ROCK 0.4 WNW, NJ US                                    0.00  \n",
       "GLEN ROCK 0.7 SSE, NJ US                                   15.00  \n",
       "HARRISON 0.3 N, NJ US                                      13.00  \n",
       "HARRISON, NJ US                                            13.00  \n",
       "HAWTHORNE 0.4 S, NJ US                                     14.00  \n",
       "...                                                          ...  \n",
       "PARSIPPANY TROY HILLS TWP 1.5, NJ US                        2.00  \n",
       "PLAINEDGE 0.4 WSW, NY US                                    3.00  \n",
       "PLAINVIEW 0.4 ENE, NY US                                   10.00  \n",
       "PUTNAM LAKE, CT US                                          0.00  \n",
       "RED BANK 0.6 ENE, NJ US                                     0.00  \n",
       "RINGWOOD 3.0 SSE, NJ US                                     1.00  \n",
       "RIVER EDGE 0.4 NNE, NJ US                                   0.00  \n",
       "SADDLE ROCK 3.4 WSW, NY US                                  7.00  \n",
       "SOUTH PLAINFIELD 0.7 NNE, NJ US                             1.00  \n",
       "SPRINGFIELD TWP 0.7 NNE, NJ US                              0.00  \n",
       "STAMFORD 4.2 S, CT US                                       4.00  \n",
       "STATEN ISLAND 1.4 SE, NY US                                14.00  \n",
       "STATEN ISLAND 4.5 SSE, NY US                                2.00  \n",
       "SYOSSET 2.0 SSW, NY US                                      9.00  \n",
       "SYOSSET, NY US                                              6.00  \n",
       "TENAFLY 1.3 W, NJ US                                       15.00  \n",
       "TENAFLY 1.6 NW, NJ US                                       1.00  \n",
       "TETERBORO AIRPORT, NJ US                                    9.00  \n",
       "VALLEY STREAM 0.6 SE, NY US                                 9.00  \n",
       "WANAQUE RAYMOND DAM, NJ US                                  8.00  \n",
       "WANTAGH 0.3 ESE, NY US                                     11.00  \n",
       "WANTAGH 1.1 NNE, NY US                                      9.00  \n",
       "WAYNE TWP 0.8 SSW, NJ US                                    5.00  \n",
       "WEST CALDWELL TWP 1.3 NE, NJ US                            10.00  \n",
       "WEST NYACK 1.3 WSW, NY US                                  11.00  \n",
       "WESTFIELD 0.6 NE, NJ US                                     9.00  \n",
       "WOODBRIDGE TWP 1.1 ESE, NJ US                              11.00  \n",
       "WOODBRIDGE TWP 1.1 NNE, NJ US                               7.00  \n",
       "WOODBRIDGE TWP 3.0 NNW, NJ US                               0.00  \n",
       "total observations of snow                                667.00  \n",
       "\n",
       "[99 rows x 13 columns]"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "snow_data = weather.query('datatype == \"SNOW\"')\n",
    "pd.crosstab(\n",
    "    index=snow_data.station_name,\n",
    "    columns=snow_data.index.month,\n",
    "    colnames=['month'],\n",
    "    values=snow_data.value,\n",
    "    aggfunc=lambda x: (x > 0).sum(),\n",
    "    margins=True, # show row and column subtotals\n",
    "    margins_name='total observations of snow' # name the subtotals\n",
    ")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
